package work.javac.exec;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.HyperlinkData;
import com.alibaba.excel.metadata.data.RichTextStringData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.IndexedColors;
import work.javac.bean.Column;
import work.javac.bean.Excel;
import work.javac.bean.Table;
import work.javac.common.Log;
import work.javac.common.PathUtil;
import work.javac.common.database.SQLExecute;
import work.javac.common.database.utils.DBUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

public class Compare {

    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
    private static final String INIT = "compare_";
    private static final String TYPE = ".xlsx";
    private static final String ERROR1 = "请检查参数(-d)数据源至少为2个!";
    private static LinkedHashMap<String, List<Table>> DB_TABLES = new LinkedHashMap();

    public static void start(Map map) throws SQLException {
        String db = (String) map.get("db");
        String table = (String) map.get("table");
        if(db == null){
            Log.err(ERROR1);
            System.exit(1);
        }
        String[] dbs = db.split(",");
        if(dbs.length < 2){
            Log.err(ERROR1);
            System.exit(1);
        }
        for(String database : dbs){
            Log.info("开始获取数据源表结构:%s", database);
            try(Connection conn = DBUtils.getConn(database)){
                List<Table> tables = SQLExecute.getTables(conn, table);
                DB_TABLES.put(database, tables);
            }
        }
        compare();
    }

    private static void compare(){
        Log.info("开始表结构对比");
        List<Excel> datas = new ArrayList();
        TreeMap<String, Excel> excelHashMap = new TreeMap<>();
        HashMap<String, LinkedHashMap<String, List>> tableColumns = new HashMap<>();
        Short[] color = new Short[]{IndexedColors.ROYAL_BLUE.getIndex(), IndexedColors.TEAL.getIndex(), IndexedColors.LIME.getIndex(), IndexedColors.GREEN.getIndex(), IndexedColors.MAROON.getIndex()};
        AtomicInteger i = new AtomicInteger();
        AtomicInteger finalI = i;
        DB_TABLES.forEach((db, tables) -> {
            tables.forEach(item -> {
                String tableName = item.getTableName();

                // 数据源
                int head = 1;
                LinkedHashMap<String, List> columnList = new LinkedHashMap<>();
                if(tableColumns.containsKey(tableName)){
                    columnList = tableColumns.get(tableName);
                    head = (int) columnList.get("head_seq").get(0) + 9;
                }
                List column = Arrays.asList(new Object[50]);
                if(columnList.containsKey("head")){
                    column = columnList.get("head");
                }
                column.set(head++,"数据源名:");
                column.set(head++, db);
                columnList.put("head", column);
                int finalHead = head;
                columnList.put("head_seq", new ArrayList(){{add(finalHead);}});

                // 字段名
                head = 0;
                if(tableColumns.containsKey(tableName)){
                    head = (int) columnList.get("column_seq").get(0) + 1;
                }
                column = Arrays.asList(new Object[50]);
                if(columnList.containsKey("column")){
                    column = columnList.get("column");
                }
                column.set(head++, "序号");
                column.set(head++, "字段名");
                column.set(head++, "字段备注");
                column.set(head++, "字段类型");
                column.set(head++, "字段长度");
                column.set(head++, "小数位");
                column.set(head++, "不能为空");
                column.set(head++, "是否主键");
                column.set(head++, "是否索引");
                column.set(head++, "字段默认值");
                columnList.put("column", column);
                int finalHead1 = head;
                columnList.put("column_seq", new ArrayList(){{add(finalHead1);}});

                List<Column> columns = item.getColumns();
                List lens = new ArrayList();
                if(columnList.containsKey("length")){
                    lens = columnList.get("length");
                }
                lens.add(columns.size());
                columnList.put("length", lens);

                AtomicInteger seq = new AtomicInteger(1);
                LinkedHashMap<String, List> finalColumnList = columnList;
                AtomicInteger xt = new AtomicInteger(0);
                columns.forEach(col -> {
                    int x = (int) finalColumnList.get("column_seq").get(0) - 10;
                    List list = Arrays.asList(new Object[50]);
                    String columnName = col.getColumnName();
                    if(finalColumnList.containsKey(columnName)){
                        list = finalColumnList.get(columnName);
                        xt.getAndIncrement();
                    }
                    list.set(x++, seq.getAndIncrement());
                    list.set(x++, columnName);
                    list.set(x++, col.getRemarks());
                    list.set(x++, col.getTypeName());
                    list.set(x++, col.getColumnSize());
                    if(col.getDecimalDigits() != 0){
                        list.set(x, col.getDecimalDigits());
                    }
                    x++;
                    list.set(x++, col.getNullable() == 0 ? "√" : null);
                    list.set(x++, item.getPrimaryKeys().contains(col.getColumnName()) ? "√" : null);
                    AtomicReference<Boolean> ifIndexs = new AtomicReference<>(false);
                    item.getIndexs().forEach(index -> {
                        ifIndexs.set(index.getColumns().contains(columnName));
                    });
                    list.set(x++, ifIndexs.get() ? "√" : null);
                    list.set(x++, col.getColumnDef());
                    finalColumnList.put(columnName, list);
                });

                int len = finalColumnList.size() - 5;
                if(!finalColumnList.get("length").contains(len)){
                    finalColumnList.get("length").add(xt.get());
                    finalColumnList.get("length").add(len);
                }
                tableColumns.put(tableName, finalColumnList);

                Excel excel = new Excel();
                String remarks = item.getRemarks();
                if(remarks != null){
                    excel.setRemarks(remarks);
                }
                if(excelHashMap.containsKey(tableName)){
                    excel = excelHashMap.get(tableName);
                    RichTextStringData richTextStringDataValue = excel.getTableExists().getRichTextStringDataValue();
                    String textString = richTextStringDataValue.getTextString();
                    String s = textString + db + "存在;";
                    richTextStringDataValue.setTextString(s);
                    WriteFont writeFont = new WriteFont();
                    writeFont.setColor(color[finalI.get()]);
                    richTextStringDataValue.applyFont(textString.length(), s.length() - 1, writeFont);
                    return;
                }
                // 表名设置超链接
                WriteCellData<String> cellTableName = new WriteCellData<>(tableName);
                HyperlinkData hyperlinkData = new HyperlinkData();
                cellTableName.setHyperlinkData(hyperlinkData);
                hyperlinkData.setAddress(item.getTableName()+"!A1");
                hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.DOCUMENT);
                excel.setTableName(cellTableName);

                // 设置单个单元格多种样式
                WriteCellData<String> cellTableExists = new WriteCellData<>();
                cellTableExists.setType(CellDataTypeEnum.RICH_TEXT_STRING);
                RichTextStringData richTextStringData = new RichTextStringData();
                cellTableExists.setRichTextStringDataValue(richTextStringData);
                String s = db + "存在;";
                richTextStringData.setTextString(s);
                WriteFont writeFont = new WriteFont();
                writeFont.setColor(color[finalI.get()]);
                richTextStringData.applyFont(0, s.length() - 1,writeFont);
                excel.setTableExists(cellTableExists);
                excelHashMap.put(tableName, excel);
            });
            finalI.getAndIncrement();
        });

        i = new AtomicInteger(1);
        AtomicInteger finalI1 = i;
        excelHashMap.forEach((k, v) -> {
            v.setSeq(finalI1.getAndIncrement());
            List length = tableColumns.get(k).get("length");
            Integer min = (Integer) Collections.min(length);
            Integer max = (Integer) Collections.max(length);
            String s;
            WriteFont writeFont = new WriteFont();
            if(min.equals(max)){
                s = "相同;字段数:" + max;
                writeFont.setColor(IndexedColors.GREEN.getIndex());
            }else{
                s = "差异;相同字段数:" + min + "相差字段数:" + (max - min);
                writeFont.setColor(IndexedColors.RED.getIndex());
            }
            WriteCellData<String> cellColumnExists = new WriteCellData<>();
            cellColumnExists.setType(CellDataTypeEnum.RICH_TEXT_STRING);
            RichTextStringData richTextStringData = new RichTextStringData();
            cellColumnExists.setRichTextStringDataValue(richTextStringData);
            richTextStringData.setTextString(s);
            richTextStringData.applyFont(writeFont);
            v.setColumnExists(cellColumnExists);
            datas.add(v);
        });

        LocalDateTime localDateTime = LocalDateTime.now();
        String fileName = PathUtil.getFile(INIT + localDateTime.format(formatter) + TYPE).getPath();
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, Excel.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).inMemory(true).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "首页").build();
            excelWriter.write(datas, writeSheet);

            datas.forEach(item ->{
                String tableName = item.getTableName().getStringValue();
                int seq = item.getSeq();
                WriteSheet columnSheet = EasyExcel.writerSheet(seq, tableName).head(new ArrayList<>()).build();
                seq++;
                List<List> columnsDatas = new ArrayList<>();
                WriteCellData<String> cellBack = new WriteCellData<>("  点击此处返回首页  ");
                HyperlinkData hyperlinkData = new HyperlinkData();
                cellBack.setHyperlinkData(hyperlinkData);
                hyperlinkData.setAddress("首页!B" + seq);
                hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.DOCUMENT);
                columnsDatas.add(new ArrayList(){{add(null);add(cellBack);}});
                columnsDatas.add(new ArrayList(){{add(null);add("英文表名:"); add(tableName);}});
                columnsDatas.add(new ArrayList(){{add(null);add("中文表名:"); add(item.getRemarks());}});
                tableColumns.get(tableName).forEach((k,v) -> {
                    if(k.contains("seq") || k.contains("len")){
                        return;
                    }
                    columnsDatas.add(v);
                });
                excelWriter.write(columnsDatas, columnSheet);
            });
        }
        Log.info("对比完成!请查看路径:" + fileName);
    }

}
